/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import POJO.PhongPOJO;
import Utils.MySqlDataAccessHelper;
import com.mysql.jdbc.CallableStatement;
import com.mysql.jdbc.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author KimLoan
 */
public class PhongDAO {
    public boolean themPhong(PhongPOJO phg) {
        boolean kq = false;
        String sql = String.format("INSERT INTO phong(idRap,TenPhong,SoLuongGhe,TinhTrangPhong)"
                + " VALUES(%d,'%s',%d,%d)", phg.getMaRap(), phg.getTenPhong(), phg.getSoLuongGhe(),phg.getTrangThai());
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        int n = helper.executeUpdate(sql);
        if (n == 1) {
            kq = true;//Them Phong thanh cong
        }

        helper.close();
        return kq;
    }

    /**
     * Phuong Thuc Xoa Phong
     * @return
     */
    public boolean xoaPhong(String maPhong) {

        boolean kq = false;
        try {
            String sql = "delete from phong where idPhong=" + maPhong;
            // String sql = "DELETE FROM Phong WHERE maPhong ='"+  maPhong+"'";
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            int n = helper.executeUpdate(sql);
            if (n >= 1) {
                kq = true;//xoa thanh cong
            }
            helper.close();
            return kq;
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
            return kq;
        }
    }

    /**
     * Phuong Thuc Cap Nhat Phong
     */
    public boolean capNhatPhong(PhongPOJO phg) {
        boolean kq = false;

        String sql = String.format(
                "UPDATE phong SET idRap=%d,TenPhong='%s',SoLuongGhe=%d,TinhTrangPhong=%d"
                + " WHERE idPhong=%d ", phg.getMaRap(), phg.getTenPhong(), phg.getSoLuongGhe(),phg.getTrangThai(),phg.getMaPhong());
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        int n = helper.executeUpdate(sql);
        if (n == 1) {
            kq = true;//Cap Nhat Phong thanh cong
        }
        helper.close();
        return kq;
    }

    /**
     * Phuong Thuc Xem Danh Phong
     * @return
     */
    public ArrayList<PhongPOJO> layDanhPhongCacMaPhong() {
        ArrayList<PhongPOJO> ds = new ArrayList<PhongPOJO>();
        try {
            String sql = "SELECT * FROM phong";
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql);
            while (rs.next()) {
                PhongPOJO phg = new PhongPOJO();
                phg.setMaPhong(rs.getInt("idPhong")); 
                phg.setMaRap(rs.getInt("idRap"));
                phg.setTenPhong(rs.getString("TenPhong"));               
                phg.setSoLuongGhe(rs.getInt("SoLuongGhe"));
                phg.setTrangThai(rs.getInt("TinhTrangPhong"));
                ds.add(phg);
            }
            helper.close();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return ds;
    }

    /**
     * Phuong Thuc Lay Thong Tin Phong
     * @param maPhong
     * @return
     */
    public PhongPOJO layThongTinPhong(String maPhong) {
        PhongPOJO Phong = null;
        String sql = String.format("SELECT * FROM phong WHERE idPhong=%d", maPhong);
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql);
            if (rs.next()) {
                Phong = new PhongPOJO();
                 Phong.setMaPhong(rs.getInt("idPhong"));
                Phong.setTenPhong(rs.getString("TenPhong"));
                Phong.setMaRap(rs.getInt("idRap"));
                Phong.setSoLuongGhe(rs.getInt("SoLuongGhe"));
                Phong.setTrangThai(rs.getInt("TrangThaiPhong"));

            }
            helper.close();
        } catch (SQLException ex) {

            System.out.println(ex.getMessage());
        }
        return Phong;
    }
    //Ham Lay danh Phong theo the loai

    public ArrayList<PhongPOJO> layDSPhong_TheoTheLoai(int maTheLoai) {

        ArrayList<PhongPOJO> dsPhong = new ArrayList<PhongPOJO>();
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            Connection cn = (Connection) helper.getConnection();
            CallableStatement statement = (CallableStatement) cn.prepareCall("{call sp_LayDSPhong_TheoTheLoai(?)}");
            statement.setInt(1, maTheLoai);

            statement.execute();
            ResultSet rs = statement.getResultSet();
            while (rs.next()) {
                PhongPOJO Phong = new PhongPOJO();
                Phong.setMaPhong(rs.getInt("idPhong"));
                Phong.setTenPhong(rs.getString("TenPhong"));
                Phong.setMaRap(rs.getInt("idRap"));
                Phong.setSoLuongGhe(rs.getInt("SoLuongGhe"));
                Phong.setTrangThai(rs.getInt("TrangThaiPhong"));
                dsPhong.add(Phong);

            }
            helper.close();
            return dsPhong;
        } catch (Exception ex) {
            System.out.print(ex.getMessage());
            return dsPhong;
        }
    }
}
